/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package server.DAO.MySQLDAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;
import common.DAO.ComponentDAO;
import common.Objects.Component;

/**
 * The database manipulations and getters for Component.
 * @author Richard Irons
 */
class MySQLComponentDAO implements ComponentDAO {
    /**
     * Get component returns a vector of components from the database
     * @return A vector of the components from the database
     */
    public Vector<Component> getComponents() {
        Vector<Component> components = new Vector<Component>();
        try {
            Connection c = MySQLDAOFactory.createConnection();
            if (c != null) {
                Statement statement = c.createStatement();
                ResultSet rs = statement.executeQuery(
                        "SELECT * FROM " + MySQLDAOFactory.DBNAME + "." + "COMPONENTS;");

                if (rs.isBeforeFirst()){
                    rs.next();
                }
                if (rs.first()){
                    do {
                        Component tempComponent = new Component();

                        tempComponent.setId(rs.getInt("Id"));
                        tempComponent.setName(rs.getString("Name"));
                        tempComponent.setDescription(rs.getString("Description"));
                        tempComponent.setCurrentStock(rs.getInt("Current_Stock"));
                        tempComponent.setPrice(rs.getDouble("Price"));
                        tempComponent.setCategoryId(rs.getInt("Category_Id"));

                        components.add(tempComponent);
                    }
                    while (rs.next());
                }
                c.close();
            }
        } catch(Exception e){e.printStackTrace();}
        return components;
    }
    /**
     * Get a single component from the DB where the id is the same as the input
     * @param id - Component to get
     * @return Found component else new component
     */
    public Component getComponent(int id) {
        Component component = new Component();
        try {
            Connection c = MySQLDAOFactory.createConnection();
            if (c != null) {
                Statement statement = c.createStatement();
                ResultSet rs = statement.executeQuery(
                        "SELECT * FROM " + MySQLDAOFactory.DBNAME + "." + "COMPONENTS WHERE Id='" + id + "';");

                if (rs.isBeforeFirst()){
                    rs.next();
                }
                if (rs.first()){
                        component.setId(rs.getInt("Id"));
                        component.setName(rs.getString("Name"));
                        component.setDescription(rs.getString("Description"));
                        component.setCurrentStock(rs.getInt("Current_Stock"));
                        component.setPrice(rs.getDouble("Price"));
                        component.setCategoryId(rs.getInt("Category_Id"));
                }
                c.close();
            }
        } catch(Exception e){e.printStackTrace();}
        return component;
    }

    /**
     * Adds a component to the database. returns true if succeded, false if
     * theres was an error
     * @param component - component to add.
     * @return True if succeded, false if theres was an error
     */
    public boolean createComponent(Component component) {        
        return MySQLDAOFactory.dbUpdate(
                "INSERT INTO " + MySQLDAOFactory.DBNAME + "." + "COMPONENTS ( "
                + "Id, "
                + "Name, "
                + "Description, "
                + "Current_Stock, "
                + "Price, "
                + "Category_Id"
                + " ) VALUES ( "
                + "NULL, "
                + "'" + component.getName() + "', "
                + "'" + component.getDescription() + "', "
                + "'" + component.getCurrentStock() + "', "
                + "'" + component.getPrice() + "', "
                + "'" + component.getCategoryId() + "' "
                + ");");
    }
    /**
     * Delete a component where the id is the same as the input id, limited to 1.
     * Returns the result of the executeUpdate
     * @param id - id to delete.
     * @return The result from executeUpdate.
     */
    public boolean deleteComponent(int id) {
        MySQLDAOFactory.dbUpdate(
                    "UPDATE " + MySQLDAOFactory.DBNAME + "." + "PRODUCT_LOOKUP "
                    + "SET "
                    + "Comp_Id = '-1'"
                    + "WHERE Comp_Id = '"+id+"';");
        return MySQLDAOFactory.dbUpdate(
                "DELETE FROM " + MySQLDAOFactory.DBNAME + "." + "COMPONENTS WHERE Id = \""
                +id
                +"\" LIMIT 1;");
    }
    public boolean editComponent(Component component) {
        return MySQLDAOFactory.dbUpdate(
                "UPDATE " + MySQLDAOFactory.DBNAME + "." + "COMPONENTS  "
                + "SET "
                + "Name = '"+ component.getName() +"', "
                + "Description = '"+ component.getDescription() +"', "
                + "Current_Stock = '"+ component.getCurrentStock() +"', "
                + "Price = '"+ component.getPrice() +"', "
                + "Category_Id = '"+ component.getCategoryId()+"' "
                + "WHERE Id = '" + component.getId() + "'"
                + ";");
    }
}

